﻿using Model;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Linq;
using System.Runtime.InteropServices;
using System.Security.Cryptography;
using System.Security.Policy;
using System.Text;
using System.Threading.Tasks;

namespace DAL
{
    public class MemberInfoDAL
    {

        public DataTable SelectMember(int pageIndex, int pageSize)
        {
            string sql = "SELECT mid, mname, gender, type, discount, money, points, birthday, phoneNum, registDate, endDate, removeFlag FROM MemberInfo where removeFlag = 0 limit @start, @pageSize";
            SQLiteParameter[] sQLiteParameters = new SQLiteParameter[]
            {
                new SQLiteParameter("@start", (pageIndex - 1) * pageSize),
                new SQLiteParameter("@pageSize", pageSize)
            };

            return SQLiteHelper.ExecuteFill(sql,sQLiteParameters);
        }

        public int SelectMemberCount(int pageSize)
        {
            string sql = "select ceiling(count(*) / (@pageSize * 1.0)) from MemberInfo where removeFlag = 0";
            SQLiteParameter[] sQLiteParameters = new SQLiteParameter[]
            {
                new SQLiteParameter("@pageSize", pageSize)
            };

            return Convert.ToInt32(SQLiteHelper.ExecuteScalar(sql, sQLiteParameters));
        }


        public int DeleteMemberInfo(int id)
        {
            string sql = "update MemberInfo set removeFlag = 1 where mid = @mid";

            SQLiteParameter[] sQLiteParameters = new SQLiteParameter[]
            {
                new SQLiteParameter("@mid", id)
            };

            return SQLiteHelper.ExecuteNonQuery(sql, sQLiteParameters);
        }


        public DataTable SelectIdOrName(int id, string name, bool isId)
        {

            if (isId)
            {
                string sql = "select * from MemberInfo where removeFlag = 0 and mid = @mid";
                SQLiteParameter[] sQLiteParameters = new SQLiteParameter[]
                {
                new SQLiteParameter("@mid", id)
                };

                return SQLiteHelper.ExecuteFill(sql, sQLiteParameters);
            }
            else
            {
                string sql = "select * from MemberInfo where removeFlag = 0 and mname like @mname";
                SQLiteParameter[] sQLiteParameters = new SQLiteParameter[]
                {
                new SQLiteParameter("@mname", "%" + name + "%")
                };

                return SQLiteHelper.ExecuteFill(sql, sQLiteParameters);


            }
        }


        public int UpdateMember(MemberInfo memberInfo)
        {
            string sql = "UPDATE MemberInfo SET mname = @mname, gender = @gender, type = @type, discount = @discount, money = @money, " + 
                "points = @points, birthday = @birthday, phoneNum = @phoneNum, registDate = @registDate, endDate = @endDate WHERE mid = @mid";
            SQLiteParameter[] sQLiteParameters = new SQLiteParameter[]
            {
                new SQLiteParameter("@mname", memberInfo.Mname),
                new SQLiteParameter("@gender", memberInfo.Gender),
                new SQLiteParameter("@type", memberInfo.Type),
                new SQLiteParameter("@discount", memberInfo.Discount),
                new SQLiteParameter("@money", memberInfo.Money),
                new SQLiteParameter("@points", memberInfo.Points),
                new SQLiteParameter("@birthday", memberInfo.Birthday),
                new SQLiteParameter("@phoneNum", memberInfo.PhoneNum),
                new SQLiteParameter("@registDate", memberInfo.RegistDate),
                new SQLiteParameter("@endDate", memberInfo.EndDate),
                new SQLiteParameter("@mid", memberInfo.Mid)

            };
            return SQLiteHelper.ExecuteNonQuery(sql, sQLiteParameters);
        
        }


        //根据会员尾号查询会员信息
        public MemberInfo SelectMemberByEndnum(string phoneNum)
        {
            MemberInfo memberInfo = null;
            string sql = "select mid, mname, discount, money, points from MemberInfo where removeFlag = 0 and PhoneNum like @phoneNum";

            SQLiteParameter[] sQLiteParameters = new SQLiteParameter[]
            {
                new SQLiteParameter("@phoneNum", "_______" + phoneNum)
            };

            SQLiteConnection conn;

            using (SQLiteDataReader sQLiteDataReader = SQLiteHelper.ExecuteReader(out conn, sql, sQLiteParameters))
            {
                if (sQLiteDataReader.Read())
                {
                    memberInfo = new MemberInfo();
                    memberInfo.Mid = sQLiteDataReader.GetInt32(0);
                    memberInfo.Mname = sQLiteDataReader.GetString(1);
                    memberInfo.Discount = sQLiteDataReader.GetDouble(2);
                    memberInfo.Money = sQLiteDataReader.GetDouble(3);
                    memberInfo.Points = sQLiteDataReader.GetInt32(4);
                }
            }

            conn.Close();

            return memberInfo;
        }


        //更新会员的余额
        public int SubMoney(int mid, double money)
        {
            string sql = "update memberInfo set money = money - @money, points = points + @ponits where mid = @mid";

            SQLiteParameter[] sQLiteParameters = new SQLiteParameter[]
            {
                new SQLiteParameter("@money", money),
                new SQLiteParameter("@ponits", money),
                new SQLiteParameter("@mid", mid)
            };

            return SQLiteHelper.ExecuteNonQuery(sql, sQLiteParameters);
        }
    }
}
